﻿using HBase;
using System;
using System.Text;
namespace hCommand {
    public class PageDialect {
        public static String pageSql(GKDBType dbType ,string sql ,int start ,int end ,string orderBy) {
            if( GKDBType.oracle == dbType ) {
                return createOraclePageSql(sql ,start ,end ,orderBy);
            }
            if( GKDBType.sqlserver == dbType ) {
                return createSqlserverPageSql(sql ,start ,end ,orderBy);
            }

            if( GKDBType.mySql == dbType ) {
                return createMysqlPageSql(sql ,start ,end ,orderBy);
            }
            return "";
        }
        static String createOraclePageSql(string sql ,int start ,int end ,string orderBy) {
            sql = @"SELECT *  FROM (SELECT tt.*, ROWNUM AS rowno  FROM (  SELECT t.* FROM " + sql;
            sql += @" ORDER BY " + orderBy + ") tt WHERE ROWNUM <= " + end + ") table_alias WHERE table_alias.rowno >= " + start;
            String countSql = "select COUNT(*) FROM (" + sql + ") as t";
            return sql + ";" + countSql;
        }

        static String createMysqlPageSql(string sql ,int start ,int end ,string orderBy) {
            start -= 1;
            String countSql = sql.ToUpper();
            countSql = countSql.Substring(countSql.IndexOf("FROM"));
            countSql = "select  COUNT(*) " + countSql;

            sql += @" ORDER BY " + orderBy + " LIMIT " + start + "," + (end - start);

            return sql + ";" + countSql;
        }
        static String createSqlserverPageSql(string sql ,int start ,int end ,string orderBy) {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSqlCount = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if( !string.IsNullOrEmpty(orderBy) ) {
                strSql.Append("order by T." + orderBy);
            }
            strSql.Append(")AS pRowno, T.*  from (");
            strSql.Append(sql);
            strSql.Append(")T ) TT");
            strSql.AppendFormat(" WHERE TT.pRowno>= {0} and  TT.pRowno<={1}  ORDER BY TT.pRowno " ,start ,end);
            strSqlCount.Append("select COUNT(*) FROM (" + sql + ") as t");
            return strSql.ToString() + ";" + strSqlCount.ToString();
        }
    }
}
